/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package person.report;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;
import javax.naming.NamingException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import system.general.Exc;
import system.general.Logged;
import system.general.Persistencia;

/**
 *
 * @author gilberto-CIDITIC
 */
@ManagedBean(name = "person_academyReport")
@RequestScoped
public class Academy {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private ResultSet rsts;
    private String sql, tableName;
    private system.general.Label label = new system.general.Label();

    public Academy() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
        tableName = "reportes.persona_academia_" + String.valueOf(logged.getLoggedId());
    }

    public void report() throws Exc, SQLException, NamingException, Exception {
        try {
            system.general.Path path = new system.general.Path();
            //Elimina la tabla
            dropTable();

            //Crea la tabla 
            createTable();

            //inserta los datos
            loadData();

            Map params = new HashMap();
            params.put("table", tableName);
            params.put("SUBREPORT_DIR", path.getPersonReportPath());
            String jasperReport = path.getPersonReportPath() + "academica.jasper";
            this.persistencia.dbConnect();
            createReport(jasperReport, params, persistencia.getConn());
        } catch (JRException ex) {
        } catch (Exception ex) {
        } finally {
            this.persistencia.dbDisconnect();
        }
    }

    private void loadData() throws Exc, SQLException, NamingException, Exception {
        try {

            sql = "SELECT pa.id_persona, lp.nombre AS pais, pa.idl_nivel AS nivel, pa.inicio, pa.fin, "
                    + "pa.titulo, pa.especialidad, pa.institucion FROM public.v_persona_academia pa "
                    + "INNER JOIN public.v_localizacion_pais lp ON (pa.id_pais = lp.id) "
                    + "WHERE pa.id_persona = " + logged.getMasterId();

            rsts = persistencia.executeRsts(sql);
            while (rsts.next()) {
                sql = "INSERT INTO " + tableName + " "
                        + "(title, id_persona, pais, nivel, inicio, fin,"
                        + "titulo, especialidad, institucion) "
                        + "VALUES ('" + label.name("person.report", "title2") + "',"
                        + rsts.getInt("id_persona") + ", "
                        + "'" + rsts.getString("pais") + "',"
                        + "'" + label.name("person.academy", "nivel" + rsts.getString("nivel")) + "',"
                        + "'" + rsts.getDate("inicio") + "',"
                        + "'" + rsts.getDate("fin") + "',"
                        + "'" + rsts.getString("titulo") + "',"
                        + "'" + rsts.getString("especialidad") + "',"
                        + "'" + rsts.getString("institucion") + "')";
                persistencia.executeUpdate(sql);
            }
        } catch (Exc ex) {
            String e = ex.toString();
        } catch (SQLException ex) {
            String e = ex.toString();
        } catch (Exception ex) {
            String e = ex.toString();
        } finally {
            persistencia.dbDisconnect();
        }
    }

    private void dropTable() throws Exc, SQLException, NamingException, Exception {
        sql = "DROP TABLE IF EXISTS " + tableName;
        persistencia.executeUpdate(sql);
    }

    private void createTable() throws Exc, SQLException, NamingException, Exception {
        sql = "CREATE TABLE " + tableName
                + " (title character varying (300), "
                + "id_persona integer, "
                + "pais character varying(50) NOT NULL, "
                + "nivel character varying(50) NOT NULL, "
                + "inicio date NOT NULL, "
                + "fin date NOT NULL, "
                + "titulo character varying(200) NOT NULL, "
                + "especialidad character varying(200), "
                + "institucion character varying(200)) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + tableName
                + " OWNER TO core;";

        persistencia.executeUpdate(sql);
    }

    public void createReport(String archivoJasper, Map params, Connection ds) throws JRException, IOException {
        JasperPrint jasperPrint = JasperFillManager.fillReport(archivoJasper, params, ds);
        HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
        JasperExportManager.exportReportToPdfStream(jasperPrint, servletOutputStream);
    }

    public String getTableName() {
        return tableName;
    }
}
